8  Health System Data Tables

8.1 Table name: LOCATION

8.1.1 Reading from environment variable (SHAK_code) and SHAK code lookup file

Note: to avoid adding in locations that will not be used by the ETL, we will ask the user via an environment variable what the SHAK code associated with the dataset is.

Destination Field Source Field Logic Comment
location_id Hospital_SHAK_code (SHAK code look up file) Autogenerate integer Use the SHAK_code environment variable to do the lookup in the SHAK code lookup file against the SHAK_code field. Only create one distinct record for the returned entry.
address_1 NULL
address_2 NULL
city NULL
state NULL
zip postal_code (SHAK code look up file) Use postal code (4 digits)
county NULL
location_source_value Hospital_SHAK_code (environment variable) <column_name>|<column_value>
country_concept_id 4330435 [Denmark]
conntry_source_value NULL
latitude NULL
longitude NULL

8.2 Table name: CARE_SITE

8.2.1 Reading from environment variable (SHAK_code) and SHAK code lookup file

Note: to avoid adding in locations that will not be used by the ETL, we will ask the user via an environment variable what the SHAK code associated with the dataset is.

Destination Field Source Field Logic Comment
care_site_id Autogenerate integer Use the SHAK_code environment variable to do the lookup in the SHAK code lookup file against the SHAK_code field. Only create a record for the returned entry.
care_site_name department_name (SHAK code look up file) use the department name from the SHAK lookup file
place_of_service_conce pt_id department_type (SHAK code look up file) First join the SHAK_codes (env variable and the SHAK code look up file) then look up the department_type in the concept_look up file by joining on the source_code field and the department_type and filtering on ‘care_site’, use the corresponding standard_concept_id (lookup based on SHAK department code) E.g. 32037 [Intensive Care]
location_id SHAK_code (environment variable) Join in the location table using the location_source_value and the SHAK_code (<column_name>|<column_value> format) to find the corresponding location_id
care_site_source_value SHAK_code (environment variable) <column_name>|<column_value> SHAK code encodes hospital + department
place_of_service_source_value department_type (SHAK code look up file) <column_name>|<column_value>